package ua.amsoft.brelok.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * @author Tair Mustafaiev
 */
public class DatabaseOpenHelper extends SQLiteOpenHelper {
    
    // DepartamentUsageReports table
    public static final String DUR_TABLE_NAME = "DepartamentUsageReports";
    public static final String DUR_ID_COLUMD = "dur_id";
    public static final String DUR_FILE_COLUMN = "dur_file";
    public static final String DUR_DATE_UPDATED_COLUMN = "dur_updated_date";
    
    // DepartamentInputDocsReports
    public static final String DIDR_TABLE_NAME = "DepartamentInputDocsReports";
    public static final String DIDR_ID_COLUMD = "didr_id";
    public static final String DIDR_FILE_COLUMN = "didr_file";
    public static final String DIDR_DATE_UPDATED_COLUMN = "didr_updated_date";
    
    // DepartamentOutputDocsReports
    public static final String DODR_TABLE_NAME = "DepartamentOutputDocsReports";
    public static final String DODR_ID_COLUMD = "dodr_id";
    public static final String DODR_FILE_COLUMN = "dodr_file";
    public static final String DODR_DATE_UPDATED_COLUMN = "dodr_updated_date";
    
    // DepartamentExecutionMandateReports
    public static final String DEMR_TABLE_NAME = "DepartamentExecutionMandateReports";
    public static final String DEMR_ID_COLUMD = "demr_id";
    public static final String DEMR_FILE_COLUMN = "demr_file";
    public static final String DEMR_DATE_UPDATED_COLUMN = "demr_updated_date";
    
    // CardHistoryReports
    public static final String CHR_TABLE_NAME = "CardHistoryReports";
    public static final String CHR_ID_COLUMD = "chr_id";
    public static final String CHR_FILE_COLUMN = "chr_file";
    public static final String CHR_DATE_UPDATED_COLUMN = "chr_updated_date";
    
    // UserInputDocsReports
    public static final String UIDR_TABLE_NAME = "UserInputDocsReports";
    public static final String UIDR_ID_COLUMD = "uidr_id";
    public static final String UIDR_FILE_COLUMN = "uidr_file";
    public static final String UIDR_DATE_UPDATED_COLUMN = "uidr_updated_date";
    
    // UserOutputDocsReports
    public static final String UODR_TABLE_NAME = "UserOutputDocsReports";
    public static final String UODR_ID_COLUMD = "uodr_id";
    public static final String UODR_FILE_COLUMN = "uoidr_file";
    public static final String UODR_DATE_UPDATED_COLUMN = "uodr_updated_date";
    
    // table creation script template
    private static final String CREATE_TABLE = "CREATE TABLE %s (%s INTEGER PRIMARY KEY, %s BLOB, %s TIMESTAMP)";
    // database name
    private static final String BRELOK_DB_NAME = "brelok";
    // database version
    private static final int DATABASE_VERSION = 2;
    
    /**
     * Constructor
     * 
     * @param context - {@link Context}
     */
    public DatabaseOpenHelper(final Context context) {
        super(context, DatabaseOpenHelper.BRELOK_DB_NAME, null, DatabaseOpenHelper.DATABASE_VERSION);
        
    }
    
    @Override
    public void onCreate(final SQLiteDatabase db) {
        db.execSQL(durTableCreateQuery());
        db.execSQL(didrTableCreateQuery());
        db.execSQL(dodrTableCreateQuery());
        db.execSQL(demrTableCreateQuery());
        db.execSQL(chrTableCreateQuery());
        db.execSQL(uidrTableCreateQuery());
        db.execSQL(uodrTableCreateQuery());
    }
    
    @Override
    public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + DatabaseOpenHelper.DUR_TABLE_NAME);
        this.onCreate(db);
    }
    
    /**
     * Formats the DepartamentUsageReports table creation script
     * 
     * @return formatted SQL script
     */
    private String durTableCreateQuery() {
        return formatCreationQuery(DatabaseOpenHelper.DUR_TABLE_NAME,
                DatabaseOpenHelper.DUR_ID_COLUMD, DatabaseOpenHelper.DUR_FILE_COLUMN,
                DatabaseOpenHelper.DUR_DATE_UPDATED_COLUMN);
    }
    
    /**
     * Formats the DepartamentInputDocsReports table creation script
     * 
     * @return formatted SQL script
     */
    private String didrTableCreateQuery() {
        return formatCreationQuery(DatabaseOpenHelper.DIDR_TABLE_NAME,
                DatabaseOpenHelper.DIDR_ID_COLUMD, DatabaseOpenHelper.DIDR_FILE_COLUMN,
                DatabaseOpenHelper.DIDR_DATE_UPDATED_COLUMN);
    }
    
    /**
     * Formats the DepartamentOutputDocsReports table creation script
     * 
     * @return formatted SQL script
     */
    private String dodrTableCreateQuery() {
        return formatCreationQuery(DatabaseOpenHelper.DODR_TABLE_NAME,
                DatabaseOpenHelper.DODR_ID_COLUMD, DatabaseOpenHelper.DODR_FILE_COLUMN,
                DatabaseOpenHelper.DODR_DATE_UPDATED_COLUMN);
    }
    
    /**
     * Formats the DepartamentExecutionMandateReports table creation script
     * 
     * @return formatted SQL script
     */
    private String demrTableCreateQuery() {
        return formatCreationQuery(DatabaseOpenHelper.DEMR_TABLE_NAME,
                DatabaseOpenHelper.DEMR_ID_COLUMD, DatabaseOpenHelper.DEMR_FILE_COLUMN,
                DatabaseOpenHelper.DEMR_DATE_UPDATED_COLUMN);
    }
    
    /**
     * Formats the CardHistoryReports table creation script
     * 
     * @return formatted SQL script
     */
    private String chrTableCreateQuery() {
        return formatCreationQuery(DatabaseOpenHelper.CHR_TABLE_NAME,
                DatabaseOpenHelper.CHR_ID_COLUMD, DatabaseOpenHelper.CHR_FILE_COLUMN,
                DatabaseOpenHelper.CHR_DATE_UPDATED_COLUMN);
    }
    
    /**
     * Formats the UserInputDocsReports table creation script
     * 
     * @return formatted SQL script
     */
    private String uidrTableCreateQuery() {
        return formatCreationQuery(DatabaseOpenHelper.UIDR_TABLE_NAME,
                DatabaseOpenHelper.UIDR_ID_COLUMD, DatabaseOpenHelper.UIDR_FILE_COLUMN,
                DatabaseOpenHelper.UIDR_DATE_UPDATED_COLUMN);
    }
    
    /**
     * Formats the UserOutputDocsReports table creation script
     * 
     * @return formatted SQL script
     */
    private String uodrTableCreateQuery() {
        return formatCreationQuery(DatabaseOpenHelper.UODR_TABLE_NAME,
                DatabaseOpenHelper.UODR_ID_COLUMD, DatabaseOpenHelper.UODR_FILE_COLUMN,
                DatabaseOpenHelper.UODR_DATE_UPDATED_COLUMN);
    }
    
    /**
     * Formats the table creation query according to passed names
     * 
     * @param tableName - String the name of the table
     * @param idColumnTitle - String - id column title
     * @param fileColumnTitle - String file column title
     * @param uDateColumnTitle - String updated date column title
     * @return formatted query string
     */
    private String formatCreationQuery(final String tableName, final String idColumnTitle,
            final String fileColumnTitle, final String uDateColumnTitle) {
        return String.format(DatabaseOpenHelper.CREATE_TABLE, tableName, idColumnTitle,
                fileColumnTitle, uDateColumnTitle);
    }
    
}
